package com.jsy.common.util.datasource;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * 数据库操作工具类，支持Oracle、MySQL和SQL Server
 * 支持增删改查
 */
public class DbUtils implements AutoCloseable {
    private Connection connection;
    private final String dbType;
    private final String url;
    private final String username;
    private final String password;

    // 数据库类型常量
    public static final String ORACLE = "oracle";
    public static final String MYSQL = "mysql";
    public static final String SQL_SERVER = "sqlserver";

    /**
     * 构造函数
     * 
     * @param dbType   数据库类型，支持 "oracle", "mysql", "sqlserver"
     * @param host     主机名
     * @param port     端口号
     * @param username 用户名
     * @param password 密码
     * @param database 数据库名（MySQL/SQL Server）
     * @param sid      Oracle SID
     * @throws SQLException           数据库连接异常
     * @throws ClassNotFoundException 驱动类未找到异常
     */
    public DbUtils(String dbType, String host, int port, String username, String password, 
                   String database, String sid) throws SQLException, ClassNotFoundException {
        this.dbType = dbType.toLowerCase();
        this.username = username;
        this.password = password;
        
        // 根据数据库类型构建URL并加载驱动
        switch (this.dbType) {
            case ORACLE:
                this.url = String.format("jdbc:oracle:thin:@%s:%d:%s", host, port, sid);
                Class.forName("oracle.jdbc.OracleDriver");
                break;
            case MYSQL:
                this.url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC", 
                                        host, port, database);
                Class.forName("com.mysql.cj.jdbc.Driver");
                break;
            case SQL_SERVER:
                this.url = String.format("jdbc:sqlserver://%s:%d;databaseName=%s", host, port, database);
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                break;
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + dbType);
        }
        
        // 建立连接
        connect();
    }
    
    /**
     * 从配置文件加载数据库连接参数
     * 
     * @param configPath 配置文件路径
     * @throws IOException            文件读取异常
     * @throws SQLException           数据库连接异常
     * @throws ClassNotFoundException 驱动类未找到异常
     */
    public DbUtils(String configPath) throws IOException, SQLException, ClassNotFoundException {
        Properties props = new Properties();
        try (InputStream is = getClass().getResourceAsStream(configPath)) {
            if (is == null) {
                throw new IOException("配置文件不存在: " + configPath);
            }
            props.load(is);
        }
        
        this.dbType = props.getProperty("db.type").toLowerCase();
        this.username = props.getProperty("db.username");
        this.password = props.getProperty("db.password");
        
        // 根据数据库类型构建URL并加载驱动
        switch (this.dbType) {
            case ORACLE:
                String host = props.getProperty("db.host");
                int port = Integer.parseInt(props.getProperty("db.port"));
                String sid = props.getProperty("db.sid");
                this.url = String.format("jdbc:oracle:thin:@%s:%d:%s", host, port, sid);
                Class.forName("oracle.jdbc.OracleDriver");
                break;
            case MYSQL:
                host = props.getProperty("db.host");
                port = Integer.parseInt(props.getProperty("db.port"));
                String database = props.getProperty("db.database");
                this.url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC", 
                                        host, port, database);
                Class.forName("com.mysql.cj.jdbc.Driver");
                break;
            case SQL_SERVER:
                host = props.getProperty("db.host");
                port = Integer.parseInt(props.getProperty("db.port"));
                database = props.getProperty("db.database");
                this.url = String.format("jdbc:sqlserver://%s:%d;databaseName=%s", host, port, database);
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                break;
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + dbType);
        }
        
        // 建立连接
        connect();
    }
    
    /**
     * 建立数据库连接
     *
     * @throws SQLException 数据库连接异常
     */
    private void connect() throws SQLException {
        connection = DriverManager.getConnection(url, username, password);
        System.out.println("成功连接到 " + dbType.toUpperCase() + " 数据库");
    }
    
    /**
     * 关闭数据库连接
     */
    @Override
    public void close() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println(dbType.toUpperCase() + " 数据库连接已关闭");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 执行SQL查询语句
     * 
     * @param sql    SQL查询语句
     * @param params 查询参数
     * @return 查询结果列表，每个元素是一个Map，表示一行数据
     * @throws SQLException SQL执行异常
     */
    public List<Map<String, Object>> query(String sql, Object... params) throws SQLException {
        List<Map<String, Object>> result = new ArrayList<>();
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            // 设置查询参数
            setParams(pstmt, params);
            
            // 执行查询
            try (ResultSet rs = pstmt.executeQuery()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                
                // 处理查询结果
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        row.put(metaData.getColumnName(i), rs.getObject(i));
                    }
                    result.add(row);
                }
            }
        }
        
        return result;
    }
    
    /**
     * 执行SQL更新语句（INSERT、UPDATE、DELETE）
     * 
     * @param sql    SQL更新语句
     * @param params 更新参数
     * @return 受影响的行数
     * @throws SQLException SQL执行异常
     */
    public int update(String sql, Object... params) throws SQLException {
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            // 设置参数
            setParams(pstmt, params);
            
            // 执行更新
            return pstmt.executeUpdate();
        }
    }
    
    /**
     * 批量执行SQL更新语句
     * 
     * @param sql        SQL更新语句
     * @param batchParam 批量参数列表，每个元素是一个参数数组
     * @return 每个SQL语句受影响的行数
     * @throws SQLException SQL执行异常
     */
    public int[] batchUpdate(String sql, List<Object[]> batchParam) throws SQLException {
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            // 添加批处理参数
            for (Object[] params : batchParam) {
                setParams(pstmt, params);
                pstmt.addBatch();
            }
            
            // 执行批处理
            return pstmt.executeBatch();
        }
    }
    
    /**
     * 执行存储过程
     * 
     * @param procName 存储过程名称
     * @param params   存储过程参数
     * @throws SQLException SQL执行异常
     */
    public void callProcedure(String procName, Object... params) throws SQLException {
        // 构建存储过程调用语句
        StringBuilder callSql = new StringBuilder("{call ");
        callSql.append(procName).append("(");
        
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                callSql.append("?,");
            }
            callSql.deleteCharAt(callSql.length() - 1); // 删除最后一个逗号
        }
        
        callSql.append(")}");
        
        try (CallableStatement cstmt = connection.prepareCall(callSql.toString())) {
            // 设置参数
            setParams(cstmt, params);
            
            // 执行存储过程
            cstmt.execute();
        }
    }
    
    /**
     * 设置PreparedStatement参数
     * 
     * @param pstmt  PreparedStatement对象
     * @param params 参数数组
     * @throws SQLException 参数设置异常
     */
    private void setParams(PreparedStatement pstmt, Object... params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
        }
    }
    
    /**
     * 开启事务
     * 
     * @throws SQLException SQL执行异常
     */
    public void beginTransaction() throws SQLException {
        connection.setAutoCommit(false);
    }
    
    /**
     * 提交事务
     * 
     * @throws SQLException SQL执行异常
     */
    public void commitTransaction() throws SQLException {
        connection.commit();
        connection.setAutoCommit(true);
    }
    
    /**
     * 回滚事务
     * 
     * @throws SQLException SQL执行异常
     */
    public void rollbackTransaction() throws SQLException {
        connection.rollback();
        connection.setAutoCommit(true);
    }

    
    /**
     * 示例代码
     * 
     * @param args 命令行参数
     */
    public static void main(String[] args) {

        // MySQL 示例
        try (DbUtils db = new DbUtils(
                DbUtils.MYSQL, 
                "localhost", 
                3306, 
                "root", 
                "password", 
                "test_db", 
                null);
//             DbUtils db = new DbUtils("/db.properties");
        ) {
            
            // 创建表
            db.update("CREATE TABLE IF NOT EXISTS users (" +
                      "id INT PRIMARY KEY AUTO_INCREMENT, " +
                      "name VARCHAR(50) NOT NULL, " +
                      "age INT)");
            
            // 插入数据
            db.update("INSERT INTO users (name, age) VALUES (?, ?)", "Alice", 25);
            
            // 批量插入
            List<Object[]> batchData = Arrays.asList(
                new Object[] {"Bob", 30},
                new Object[] {"Charlie", 35}
            );
            db.batchUpdate("INSERT INTO users (name, age) VALUES (?, ?)", batchData);
            
            // 查询数据
            List<Map<String, Object>> users = db.query("SELECT * FROM users WHERE age > ?", 30);
            System.out.println("年龄大于30的用户:");
            for (Map<String, Object> user : users) {
                System.out.println("ID: " + user.get("id") + 
                                   ", 姓名: " + user.get("name") + 
                                   ", 年龄: " + user.get("age"));
            }
            
            // 事务示例
            try {
                db.beginTransaction();
                db.update("UPDATE users SET age = age + 1 WHERE name = ?", "Alice");
                db.update("DELETE FROM users WHERE name = ?", "Bob");
                db.commitTransaction();
                System.out.println("事务执行成功");
            } catch (SQLException e) {
                db.rollbackTransaction();
                System.out.println("事务回滚: " + e.getMessage());
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        // Oracle 示例（需要正确配置Oracle环境）
        // try (DbUtils db = new DbUtils(
        //         DbUtils.ORACLE, 
        //         "localhost", 
        //         1521, 
        //         "system", 
        //         "password", 
        //         null, 
        //         "ORCL")) {
        //     // 执行Oracle操作
        // } catch (Exception e) {
        //     e.printStackTrace();
        // }
        
        // SQL Server 示例（需要正确配置SQL Server环境）
        // try (DbUtils db = new DbUtils(
        //         DbUtils.SQL_SERVER, 
        //         "localhost", 
        //         1433, 
        //         "sa", 
        //         "password", 
        //         "test_db", 
        //         null)) {
        //     // 执行SQL Server操作
        // } catch (Exception e) {
        //     e.printStackTrace();
        // }
    }
}    